/******************************************************************************
 * Copyright (C) 2014 ShenZhen YiHua Computer Co., Ltd.
 * All Rights Reserved.
 * 本软件为深圳怡化电脑股份有限公司开发研制。未经本公司正式书面同意，其他任何个人、团体
 * 不得使用、复制、修改或发布本软件.
 *****************************************************************************/
package net.dgg.dggcloud.core.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelTool
{
    /**
     * *
     * 
     * @param filename 保存到客户端的文件名
     * @param title 标题行 例：String[]{"名称","地址"}
     * @param key 从查询结果List取得的MAP的KEY顺序，需要和title顺序匹配，例：String[]{"name","address"}
     * @param values 结果集
     * @param httpServletResponse
     * @throws IOException
     */
    public static void createN(String filename, String[] title, String[] key,
            @SuppressWarnings("rawtypes") List<Map> values, HttpServletResponse httpServletResponse)
    {
    	OutputStream servletOutputStream = null;
        try
        {
//            String filename2 = new String(filename.getBytes("GBK"), "ISO8859-1");
            //servletOutputStream = httpServletResponse.getOutputStream();
            servletOutputStream = new BufferedOutputStream(httpServletResponse.getOutputStream());
            HSSFWorkbook workbook = null;//创建工作簿
//            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + filename2);
//            httpServletResponse.setContentType("application/x-download");
            httpServletResponse.setContentType("application/octet-stream; charset=UTF-8");
//            httpServletResponse.setContentType("application/octet-stream");
            httpServletResponse.setHeader("Content-Disposition", "attachment; filename=\""
                    + new String(filename.getBytes("GBK"), "ISO8859-1") + "\"");
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();//创建页
            HSSFRow row = null;
            HSSFCell cell = null;
            row = sheet.createRow((short) 0);
            for (int i = 0; title != null && i < title.length; i++)
            {
                cell = row.createCell(i);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new HSSFRichTextString(title[i]));
            }
            @SuppressWarnings("rawtypes")
            Map map = null;
            for (int i = 0; values != null && i < values.size(); i++)
            {
                row = sheet.createRow(i + 1);
                map = values.get(i);
                for (int i2 = 0; i2 < key.length; i2++)
                {
                    cell = row.createCell(i2);
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    if (map.get(key[i2]) == null)
                    {
                        cell.setCellValue(new HSSFRichTextString(""));
                    }
                    else
                    {
                        cell.setCellValue(new HSSFRichTextString(map.get(key[i2]).toString()));
                    }
                }
            }
            workbook.write(servletOutputStream);
           /* //获取一个向Response对象写入数据的流,当tomcat服务器进行响应的时候，会将Response中的数据写给浏览器
            String data = "导出成功";
            OutputStream stream = httpServletResponse.getOutputStream();
            stream.write(data.getBytes("UTF-8"));*/
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (null != servletOutputStream)
                {
                    servletOutputStream.flush();
                    servletOutputStream.close();
                }
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }

        }
    }
    
    /** 
     * @Title: changeToString 
     * @Description: 将Cell对象转换成String类型（导入时调用）
     * @param cell
     * @return
     * @return String    返回类型 
     */
    public static String cellValue(Cell cell)
    {
        String strCell = "";
        if (cell != null)
        {
            switch (cell.getCellType())
            {
                case Cell.CELL_TYPE_FORMULA:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    strCell = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell))
                    {
                        Date d = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                        //DateFormat format= new SimpleDateForma;
                        strCell = formater.format(d);
                        //只有日期的转化
                        strCell = strCell.contains("00:00")?strCell.substring(0,10):strCell;
                    }
                    else
                    {
                        strCell = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    strCell = cell.getStringCellValue();
                    break;
                default:
            }
        }
        return strCell.trim();
    }
    
   
    /** 
     * @Title: cellToString 
     * @Description: 根据索引返回行中某个单元格的值
     * @param row
     * @param cellIndex
     * @return
     * @return String    返回类型 
     */
    public static String cellValue(Row row, int cellIndex)
    {
        return cellValue((Cell) row.getCell(cellIndex));
    }
    
    /** 
     * @Title: cellValueStr 
     * @Description: 读取单元格的文本，如果是数字将替换“.0”
     * @param row
     * @param cellIndex
     * @return
     * @return String    返回类型 
     */
    public static String cellValueStr(Row row, int cellIndex)
    {
        String value = cellValue((Cell) row.getCell(cellIndex));
        if(StringUtils.isNotBlank(value))
            value = value.replace(".0", "");
        return value;
    }
    
    /** 
     * @Title: cellTitleCheck 
     * @Description: 检查Excel文件的某个列的列名是否与传入的title一致 
     * @param row
     * @param cellIndex
     * @param title
     * @return
     * @return String    返回类型 
     */
    public static String cellTitleCheck(Row row, int cellIndex, String title)
    {
        String result = "";
        String s = ExcelTool.cellValue(row, cellIndex);
        if (!StringUtils.isNotBlank(s) || !s.equals(title))
        {
            result = "第" + (cellIndex + 1) + "列必须为["+title+"]列<br/>";
        }
        return result;
    }

    /** 
     * @Title: cellValueNotBlankNumber 
     * @Description: Excel单元格非空、小数检查 
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueNotBlankNumber(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s))
        {
            if (StringUtils.isNumber(s))
            {
                objErr[cellIndex] = MathUtil.convertToDecimal(new BigDecimal(s), 2);
            }
            else
            {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为数字的"+title+"\r\n";
            }
        }
        else
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "不能为空\r\n";
        }
        return result;
    }
    
    /** 
     * @Title: cellValueNotBlankNumber 
     * @Description: Excel单元格可空、小数检查 
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueBlankNumber(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s))
        {
            if (StringUtils.isNumber(s))
            {
                objErr[cellIndex] = MathUtil.convertToDecimal(new BigDecimal(s), 2);
            }
            else
            {
                result = false;
                objErr[errorIndex] = objErr[errorIndex]  +title+"必须为数字"+"\r\n";
            }
        }
        return result;
    }
    
    /** 
     * @Title: cellValueBlank 
     * @Description: Excel单元格非空检查
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueNotBlank(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s.replace(".0", "");
        if (StringUtils.isNotBlank(s))
        {
        }
        else
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "不能为空\r\n";
        }
        return result;
    }
    
    /** 
     * @Title: cellValueBlank 
     * @Description: Excel单元格非空检查(字符不包含小数)
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellTextValueNotBlank(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s.replace(".0", "");
        if (StringUtils.isNotBlank(s))
        {
        }
        else
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "不能为空\r\n";
        }
        return result;
    }
    
    /** 
     * @Title: cellValueEqualsDbValue 
     * @Description: 比较两个字符串的值，并记录错误信息 
     * @param cellValue
     * @param dbValue
     * @param objErr
     * @param errorIndex
     * @param title
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueEqualsDbValue(String cellValue,String dbValue, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        if (!cellValue.equals(dbValue))
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "与数据库中数据不一致！\r\n";
        }
        return result;
    }

    /** 
     * @Title: cellValueNotBlankDate 
     * @Description: Excel单元格非空、日期检查
     * @param row
     * @param cellIndex
     * @param objErr
     * @param errorIndex
     * @param title
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueNotBlankDate(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex).replace(".", "-");
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s))
        {
            if (CalendarUtils.isValidDate(s))
            {
            }
            else
            {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] +title+ "必须为日期格式"+"（格式：年年年年-月月-日日）\r\n";
            }
        }
        else
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "不能为空\r\n";
        }
        return result;
    }

    /** 
     * @Title: cellValueNotBlankNumberPositive 
     * @Description: Excel单元格非空、小数、正数检查 
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueNotBlankNumberPositive(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s))
        {
            if (StringUtils.isNumber(s))
            {
                BigDecimal dec = StringUtils.strToDecimal(s);
                if (!StringUtils.compareToZero(dec, ">="))
                {
                    result = false;
                    objErr[errorIndex] = objErr[errorIndex] + "必须大于0的"+ title +"\r\n";
                }
                else
                {
                    objErr[cellIndex] = MathUtil.convertToDecimal(new BigDecimal(s), 2);
                }
            }
            else
            {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为数字的"+title+"\r\n";
            }
        }
        else
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "不能为空\r\n";
        }
        return result;
    }

    /** 
     * @Title: cellValueToBigDecimal 
     * @Description: 转换单元格字符为四舍五入小数
     * @param row
     * @param cellIndex
     * @return
     * @return BigDecimal    返回类型 
     */
    public static BigDecimal cellValueToBigDecimal(Row row, int cellIndex)
    {
        return MathUtil.convertToDecimal(StringUtils.strToDecimal(ExcelTool.cellValue(row, cellIndex)), 2);
    }
    
    /** 
     * @Title: cellValueBlankNumberPositive 
     * @Description: Excel单元格可空、小数、>=0检查 
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueBlankNumberPositive(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s))
        {
            if (StringUtils.isNumber(s))
            {
                BigDecimal dec = StringUtils.strToDecimal(s);
                if (!StringUtils.compareToZero(dec, ">="))
                {
                    result = false;
                    objErr[errorIndex] = objErr[errorIndex] + "必须大于0的"+ title +"\r\n";
                }
                else
                {
                    objErr[cellIndex] = MathUtil.convertToDecimal(new BigDecimal(s), 2);
                }
            }
            else
            {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为数字的"+title+"\r\n";
            }
        }
        else
        {
            result = false;
        }
        return result;
    }
    
    /** 
     * @Title: cellValueNotBlankNumber 
     * @Description: Excel单元格非空、电子邮件地址检查 
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueNotBlankEmail(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s))
        {
            if (StringUtils.isValidEmail(s))
            {
            }
            else
            {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为邮箱地址的"+title+"\r\n";
            }
        }
        else
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "不能为空\r\n";
        }
        return result;
    }
    
    /** 
     * @Title: cellValueNotBlankMobile 
     * @Description: Excel单元格非空、手机号码地址检查 
     * @param row 行对象
     * @param cellIndex 列索引
     * @param objErr 错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title 字段名
     * @return
     * @return boolean    返回类型 
     */
    public static boolean cellValueNotBlankMobile(Row row, int cellIndex, Object objErr[], int errorIndex, String title)
    {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        BigDecimal db = new BigDecimal(s);//避免电话号码读出来为科学计数法
        s = db.toPlainString();
        objErr[cellIndex] = s + "";
        if (StringUtils.isNotBlank(s))
        {
            if (StringUtils.isValidMobileNO(s))
            {
            }
            else
            {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为手机号码的"+title+"\r\n";
            }
        }
        else
        {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title +  "不能为空\r\n";
        }
        return result;
    }
}
